package au.com.somethingwritten.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class ShippingMethodDAO {
	private static Connection connection = null;
	private static Statement statement = null;
	private static ResultSet resultSet = null;
	
	public static ArrayList<ShippingMethod> getAllShippingMethods() throws Exception{
		ArrayList<ShippingMethod> allResults = new ArrayList<ShippingMethod>();
		try{
			//load the MySQL JDBC driver
			Class.forName("com.mysql.jdbc.Driver");
			//create the database connection
			connection = DriverManager
			.getConnection("jdbc:mysql://localhost/somethingwritten?"
			+ "user=laac&password=password");
			
			//create object for sending SQL statements
			statement = connection.createStatement();
			//execute statement - select all shipping methods.
			resultSet = statement.executeQuery("SELECT * FROM shippingMethods");
			
			//add results to List of Shipping Methods
		    while (resultSet.next()) {
		    	//Get shipping method details
		    	int id = resultSet.getInt("id");
		    	String type = resultSet.getString("type");
		    	double cost = resultSet.getDouble("cost");
		    	
		    	//Create new shipping method object
		    	ShippingMethod tmp = new ShippingMethod(id, type, cost);
		    	
		    	//Add to results list
		    	allResults.add(tmp);
		    }
			      
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
		
		return allResults;
	}
		
/***
 * Close all connect objects.
 */
private static void close() {
	try {
		if (resultSet != null) {
			resultSet.close();
		}
		if (statement != null) {
			statement.close();
		}
		if (connection != null) {
			connection.close();
		}
	} catch (Exception e) {
		
	}
}

}
